﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileReceiveLog]') AND type in (N'U'))
DROP TABLE [dbo].[FileReceiveLogVersions]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileReceiveLog]') AND type in (N'U'))
DROP TABLE [dbo].[FileReceiveLog]
GO




CREATE TABLE [FileReceiveLog](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[FileName] [varchar](300) NOT NULL,
	[UploadDateTime] [datetime] NOT NULL,
	[UploadUserAlias] [varchar](20) NOT NULL,
	[LengthBytes] [bigint] NOT NULL,
	[ServerHostName] [varchar](300) NOT NULL,
	[ServerHostAddress] [varchar](100) NOT NULL,
	[UserHostAddress] [varchar](100) NOT NULL,
	[IsDigitalSignInvalid] [bit] NULL,
	[DigitalSignInvalidDetail] [varchar](2000) NULL,
	[MD5Hash] [varchar](50) NOT NULL,
	[TryCount] [int] NOT NULL,
	ShortIdentificator VARCHAR (50) NOT NULL,
	IdentificationNumber VARCHAR (50) NULL,
	IdentificationTypeCode VARCHAR (3) NULL,

	CONSTRAINT U_FileName UNIQUE (FileName),

 CONSTRAINT [PK_FileReceiveLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE NONCLUSTERED INDEX [IX_FileReceiveLog_UploadUserAlias] ON [dbo].[FileReceiveLog] 
(
	[UploadUserAlias] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE TABLE FileReceiveLogVersions
(
	FileReceiveLogID BIGINT FOREIGN KEY REFERENCES FileReceiveLog (ID) NOT NULL,
	[GUID] VARCHAR (50) NOT NULL,
	[MD5Hash] [varchar](50) NOT NULL,
	[UploadDateTime] [datetime] NOT NULL,
	[UploadUserAlias] [varchar](20) NOT NULL,
	[LengthBytes] [bigint] NOT NULL,
	[ServerHostName] [varchar](300) NOT NULL,
	[ServerHostAddress] [varchar](100) NOT NULL,
	[UserHostAddress] [varchar](100) NOT NULL,
	[IsDigitalSignInvalid] [bit] NULL,
	[DigitalSignInvalidDetail] [varchar](2000) NULL,

	IsEnterpriseOK BIT NULL,
	
	ArrivalForDistributionDateTime DATETIME NULL,
	DecompressionDateTime DATETIME NULL,
	DigitalSignedFileMovedDateTime DATETIME NULL,
	ArrivalForValidationDateTime DATETIME NULL,
	ValidationErrorsXML XML NULL,
	ValidationDateTime DATETIME NULL,
	CopyToReceivedDateTime DATETIME NULL,	
	CopyToApplicationDateTime DATETIME NULL,

	IsValid BIT NULL,

	NotificationWasSend BIT NULL,
	NotificationDateTime DATETIME NULL,
	NotificationSubject VARCHAR (200) NULL,
	NotificationTo VARCHAR (400) NULL,
	NotificationBody VARCHAR (3000) NULL	
)
